﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using SAServices.Model;

namespace SAServices.DAL
{
    public class FamilyTableDAL
    {
        internal static bool DeleteFamilyTableInfo(Model.ConnectionConext connCtx, Guid familyId)
        {

            string sql = String.Format("IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =OBJECT_ID('ZFT_{0}')) DROP TABLE [ZFT_{0}];", familyId.ToString().ToUpper());
            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }

        private static string GetFamilyTableColumns(List<ParameterInfo> parameters)
        {
            string strCols = String.Empty;
            int cnt = parameters.Count;
            for (int i = 0; i < cnt; i++)
            {
                strCols += parameters[i].ID + ",";
            }
            return strCols;
        }

        internal static bool AddFamilyTableInfo(Model.ConnectionConext connCtx, Model.FamilyTableInfo familyTableInfo)
        {
            //create family table.
            SqlConnection conn = connCtx.GetOpenSqlConn();
            using (SqlCommand command = conn.CreateCommand())
            {
                command.CommandText = "XunPan_CreateFamilyTable";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(new SqlParameter("@familyID", familyTableInfo.FamilyID));
                string strCols = GetFamilyTableColumns(familyTableInfo.Parameters);
                command.Parameters.Add(new SqlParameter("@colNames", strCols));
                command.ExecuteNonQuery();
                int ret = Convert.ToInt32(command.Parameters["@RETURN_VALUE"].Value);

                //create table failed. return.
                if (ret != 0) return false;

                for (int i = 0; i < familyTableInfo.FamilyTable.Rows.Count; i++ )
                {
                    StringBuilder sb = new StringBuilder();
                    for (int col = 0; col < familyTableInfo.Parameters.Count && col < familyTableInfo.FamilyTable.Columns.Count; col++)
                    {
                        string cellVal = Convert.ToString(familyTableInfo.FamilyTable.Rows[i][col]);
                        sb.Append("'").Append(cellVal).Append("',");
                    }
                    sb.Remove(sb.Length - 1, 1);
                    string insertSQL = String.Format("INSERT INTO [ZFT_{0}] Values ('{1}', '', {2});", familyTableInfo.FamilyID.ToString().ToUpper(), Guid.NewGuid(), sb.ToString());
                    GenDAL.ExecuteNonQuery(connCtx, insertSQL);
                }
            }

            return true;
        }

        internal static bool UpdateFamilyTableInfo(Model.ConnectionConext connCtx, Model.FamilyTableInfo familyTableInfo)
        {
            //create family table.
            SqlConnection conn = connCtx.GetOpenSqlConn();
            using (SqlCommand command = conn.CreateCommand())
            {
                //remove all data. now, create data.
                //
                string deleteSql = String.Format("DELETE FROM [ZFT_{0}]", familyTableInfo.FamilyID.ToString().ToUpper());
                command.CommandText = deleteSql;
                command.ExecuteNonQuery();


                for (int i = 0; i < familyTableInfo.FamilyTable.Rows.Count; i++)
                {
                    StringBuilder sb = new StringBuilder();
                    for (int col = 0; col < familyTableInfo.Parameters.Count + 2 && col < familyTableInfo.FamilyTable.Columns.Count; col++)
                    {
                        string cellVal = Convert.ToString(familyTableInfo.FamilyTable.Rows[i][col]);
                        sb.Append("'").Append(cellVal).Append("',");
                    }
                    sb.Remove(sb.Length - 1, 1);
                    string insertSQL = String.Format("INSERT INTO [ZFT_{0}] Values ({1});", familyTableInfo.FamilyID.ToString().ToUpper(), sb.ToString());
                    GenDAL.ExecuteNonQuery(connCtx, insertSQL);
                }
            }

            return true;
        }


        internal static DataTable GetSubFamilyTable(ConnectionConext connCtx, Guid familyId, List<ParameterValueInfo> paramConstraints)
        {
            string sql = String.Format("SELECT * FROM [ZFT_{0}] WHERE 1=1 ", familyId.ToString().ToUpper());
            foreach (ParameterValueInfo item in paramConstraints)
            {
                sql += String.Format("AND [{0}]='{1}' ", item.ID, item.Value);
            }
            sql += ";";

            DataTable dt = GenDAL.GetTable(connCtx, sql);
            dt.TableName = "SubFamilyTable";

            //remove unneccessary columns.
            //
            //dt.Columns.Remove("instanceID");
            //dt.Columns.Remove("cachedFile");

            return dt;
        }

        internal static List<Guid> GetAllInstances(ConnectionConext connCtx, Guid familyId)
        {
            List<Guid> retList = new List<Guid>();


            string sql = String.Format("SELECT instanceID FROM [ZFT_{0}] WHERE 1=1 ", familyId);

            DataTable dt = GenDAL.GetTable(connCtx, sql);
            
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++ )
                {
                   retList.Add(new Guid(Convert.ToString(dt.Rows[i]["instanceID"])));
                }
            }

            return retList;
        }
    }
}
